package com.jandar.service;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cl.mvc.util.DbUtil;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.parser.ImageRenderInfo;
import com.itextpdf.text.pdf.parser.PdfReaderContentParser;
import com.itextpdf.text.pdf.parser.RenderListener;
import com.itextpdf.text.pdf.parser.TextRenderInfo;
import com.jandar.config.AppConfig;
import com.jandar.entity.*;

import com.jandar.repository.ExcelPersonInfoRepository;
import com.jandar.repository.ExcelPersonRepository;
import com.jandar.util.DynamicQueryUtil;
import com.jandar.util.MapTransferUtil;
import com.jandar.util.RtsUtilv2;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.Data;
import okhttp3.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import static com.jandar.constant.QTableConstant.*;


// todo 优化代码生成器和代码结构
@Service
public class AnalyzeService {
    private final Map<String, String> sqlMap = new HashMap<>();

    @Autowired
    private JPAQueryFactory queryFactory;

    @Autowired
    AppConfig appConfig;

    @Autowired
    DynamicQueryUtil dynamicQueryUtil;

    @Autowired
    ExcelSourceRecordService excelSourceRecordService;
    @Autowired
    ExcelPersonService excelPersonService;
    @Autowired
    ExcelPersonInfoService excelPersonInfoService;
    @Autowired
    ExcelPersonRecordService excelPersonRecordService;
    @Autowired
    ExcelPersonCaseService excelPersonCaseService;
    @Autowired
    ExcelCaseService excelCaseService;
    @Autowired
    BillService billService;

    private final OkHttpClient okHttpClient = new OkHttpClient();
    @Autowired
    private ExcelPersonRepository excelPersonRepository;
    @Autowired
    private ExcelPersonInfoRepository excelPersonInfoRepository;

    public List<String> listTable() {
        return Arrays.asList("bill_excel_source", "bank_excel_source");
    }

    public List<Map<String, String>> listField(String tableName) {
        return dynamicQueryUtil.listField(tableName).stream().filter(item -> !item.get("fieldName").equals(item.get("fieldComment"))).collect(Collectors.toList());
    }

    // excel上传
    public void uploadFile(MultipartFile file, Integer typeId, Integer personId, String caseNum, HttpServletRequest request) {

        String auth = request.getHeader("Authorization");
        ExcelSourceRecord excelSourceRecord = new ExcelSourceRecord();
        Integer recordId = 0;

        try {
            String filename = file.getOriginalFilename();
            assert filename != null;

            String[] fileNames = filename.split("\\.");
            String suffix = fileNames[fileNames.length - 1];
            String fileName = System.currentTimeMillis() + "-" + fileNames[0] + "." + suffix;

            String savePath = appConfig.getAnalyzeAndStatisticsDir() + fileName;
            file.transferTo(new File(savePath));

            ExcelReader reader = null;
            if (suffix.equalsIgnoreCase("xlsx") || suffix.equalsIgnoreCase("xls")) {

                reader = ExcelUtil.getReader(savePath);

                Integer maxCountSheetNum = 0;
                Integer maxCount = 0;
                for (int i = 0; i < reader.getSheetCount(); i++) {
                    reader.setSheet(i);
                    int thisCount = reader.getRowCount();
                    if (thisCount > maxCount) {
                        maxCount = thisCount;
                        maxCountSheetNum = i;
                    }
                }
                reader.setSheet(maxCountSheetNum);

                if (typeId == null) {
                    reader.setIgnoreEmptyRow(false);
                    List<List<Object>> readAll = reader.read();
                    int row = 0;
                    long maxColumnCount = readAll.get(0).stream().filter(r -> String.valueOf(r).length() > 0).count();
                    for (int i = 1; i < readAll.size(); i++) {
                        long curColumnCount = readAll.get(i).stream().filter(r -> String.valueOf(r).length() > 0).count();
                        if (curColumnCount > maxColumnCount) {
                            maxColumnCount = curColumnCount;
                            row = i;
                        }
                    }
                    List<List<Object>> read = reader.read(row, row);
                    List<Map<String, Object>> result = dynamicQueryUtil.query("select type_id,count(1) as count\n" +
                            "from excel_source_type_mapping\n" +
                            "where just_display <>1 and from_key in ('" + StringUtils.join(read.get(0), "','") + "')\n" +
                            "group by type_id\n" +
                            "order by count desc limit 1;");
                    typeId = Integer.parseInt(String.valueOf(result.get(0).get("type_id")));
                }
            }
            ExcelCase excelCase = new ExcelCase();
            excelCase.setCaseNum(caseNum);
            excelCase.setName(caseNum);
            excelCaseService.saveIfNotExists(excelCase, auth);

            ExcelSourceType excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.id.eq(typeId)).fetchFirst();

            excelSourceRecord.setName(fileNames[0]);
            excelSourceRecord.setSaveName(fileName);
            excelSourceRecord.setStatus("分析中");
            excelSourceRecord.setTypeName(excelSourceType.getName());
            excelSourceRecord.setTypeId(typeId);
            excelSourceRecordService.save(excelSourceRecord, auth);
            recordId = queryFactory.select(qExcelSourceRecord.id).from(qExcelSourceRecord).orderBy(qExcelSourceRecord.id.desc()).limit(1).fetchFirst();
            excelSourceRecord.setId(recordId);
//            Long sheetNum = 0;
            Integer startRow = 1;
//            if (excelSourceType.getSheetNum() != null) {
//                sheetNum = excelSourceType.getSheetNum() - 1;
//            }
            if (excelSourceType.getStartRow() != null) {
                startRow = excelSourceType.getStartRow();
            }

            List<ExcelSourceTypeMapping> mappingList = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(typeId).and(qExcelSourceTypeMapping.toKey.isNotNull())).fetch();

            List<Map> readAll;
//            reader.setSheet(sheetNum);

            if ("职务通讯录".equals(excelSourceType.getName())) {
                readAll = reader.read(startRow - 1, startRow, Map.class);
                processJobBookByPhone(readAll, mappingList, excelSourceRecord, caseNum);
            } else if ("中国移动PDF话单".equals(excelSourceType.getName())) {
                readAll = processPDF(savePath);
                Map<String, String> nameAndPhone = readAll.get(0);
                String sendName = nameAndPhone.get("send_name");
                String sendPhone = nameAndPhone.get("send_phone");
                readAll.remove(0);
                processData(readAll, mappingList, excelSourceRecord, personId, caseNum, sendName, sendPhone, reader, auth);
            } else {
                readAll = reader.read(startRow - 1, startRow, Map.class);

                processData(readAll, mappingList, excelSourceRecord, personId, caseNum, null, null, reader, auth);
            }

            billService.generateAddress();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public JSONObject listExcelSourceType(String caseNum, Integer personId) {
        JSONArray tagList = new JSONArray();
        List<ExcelSourceType> excelSourceTypes = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.category.isNotNull()).fetch();
        Map<String, List<ExcelSourceType>> titleAndTagTitles = new TreeMap<>();
        excelSourceTypes.forEach(excelSourceType -> {
            String category = excelSourceType.getCategory();
            if (!titleAndTagTitles.containsKey(category)) {
                titleAndTagTitles.put(category, new ArrayList<>());
            }
            titleAndTagTitles.get(category).add(excelSourceType);
        });
        titleAndTagTitles.forEach((key, value) -> {
            JSONObject tagListItem = new JSONObject();
            tagListItem.put("title", key);
            JSONArray tags = new JSONArray();
            value.forEach(excelSourceType -> {
                JSONObject tagsItem = new JSONObject();
                JSONArray columns = new JSONArray();
                List<ExcelSourceTypeMapping> excelSourceTypeMappingList = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceType.getId())).fetch();
                excelSourceTypeMappingList.forEach(excelSourceTypeMapping -> {
                    JSONObject column = new JSONObject();
                    column.put("title", excelSourceTypeMapping.getFromKey());
                    column.put("key", excelSourceTypeMapping.getToKey());
                    column.put("sortable", "custom");
                    column.put("width", 200);
                    column.put("align", "center");
                    column.put("dataType", excelSourceTypeMapping.getDataType());
                    columns.add(column);
                });
                Long count = -1L;
                try {
                    count = dynamicQueryUtil.queryCount("select count(1) from " + excelSourceType.getTableName() + " where case_num=" + caseNum + " and person_id=" + personId);
                } catch (Exception ignore) {
                }
                tagsItem.put("count", count);
                tagsItem.put("title", excelSourceType.getName());
                tagsItem.put("columns", columns);
                tags.add(tagsItem);
            });
            tagListItem.put("tags", tags);
            tagList.add(tagListItem);
        });
        return result("", tagList);
    }

    public JSONObject listExcelSourceType() {
        JSONArray tagList = new JSONArray();
        List<ExcelSourceType> excelSourceTypes = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.category.isNotNull()).fetch();

        Map<String, List<ExcelSourceType>> titleAndTagTitles = new TreeMap<>();
        excelSourceTypes.forEach(excelSourceType -> {
            String category = excelSourceType.getCategory();
            if (!titleAndTagTitles.containsKey(category)) {
                titleAndTagTitles.put(category, new ArrayList<>());
            }
            titleAndTagTitles.get(category).add(excelSourceType);
        });
        titleAndTagTitles.forEach((key, value) -> {
            JSONObject tagListItem = new JSONObject();
            tagListItem.put("title", key);
            JSONArray tags = new JSONArray();
            value.forEach(excelSourceType -> {
                JSONObject tagsItem = new JSONObject();
                JSONArray columns = new JSONArray();
                List<ExcelSourceTypeMapping> excelSourceTypeMappingList = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceType.getId())).fetch();
                excelSourceTypeMappingList.forEach(excelSourceTypeMapping -> {
                    JSONObject column = new JSONObject();
                    column.put("title", excelSourceTypeMapping.getFromKey());
                    column.put("key", excelSourceTypeMapping.getToKey());
                    column.put("sortable", "custom");
                    column.put("width", 200);
                    column.put("align", "center");
                    columns.add(column);
                });
                tagsItem.put("title", excelSourceType.getName());
                tagsItem.put("columns", columns);
                tags.add(tagsItem);
            });
            tagListItem.put("tags", tags);
            tagList.add(tagListItem);
        });
        return result("", tagList);
    }

    public JSONObject pageDataByType(PageParam param) {
        String typeName = param.typeName;
        Integer personId = param.personId;
        String caseNum = param.caseNum;
        Long num = param.pageNumber;
        Long size = param.pageSize;
        Long offset = (num - 1) * size;
        ExcelSourceType excelSourceTypeRecord = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.name.eq(typeName)).fetchFirst();
        String tableName = excelSourceTypeRecord.getTableName();
        List<ExcelSourceTypeMapping> excelSourceTypeMappings = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(excelSourceTypeRecord.getId())).fetch();
        Map<String, String> columns = new TreeMap<>();
        excelSourceTypeMappings.forEach(mapping -> {
            String toKey = mapping.getToKey();
            String fromKey = mapping.getFromKey();
            columns.put(toKey, fromKey);
        });
        param.entity.put("case_num_equal", caseNum);
        param.entity.put("person_id_equal", personId);
        Long count = dynamicQueryUtil.queryCount(tableName, param.entity);
        List<Map<String, Object>> list = dynamicQueryUtil.query(tableName, columns, param.entity, param.order, offset, size);
        return result("", list, count);
    }

    public JSONObject previewSourceData(PageParam param) {
        String typeName = param.typeName;
        String field = param.field;
        String value = param.value;
        Long num = param.pageNumber;
        Long size = param.pageSize;
        Long offset = (num - 1) * size;
        BooleanBuilder booleanBuilder = new BooleanBuilder();
        booleanBuilder.and(Expressions.asString(field).eq(value));

        if ("手机话单".equals(typeName)) {
            List<BillExcelSource> list = queryFactory.selectFrom(qBillExcelSource).where(booleanBuilder).offset(offset).limit(size).fetch();
            Long count = queryFactory.select(qBillExcelSource.count()).where(booleanBuilder).fetchOne();
            return result("", list, count);
        } else if ("银行账单".equals(typeName)) {
            List<BankExcelSource> list = queryFactory.selectFrom(qBankExcelSource).where(booleanBuilder).offset(offset).limit(size).fetch();
            Long count = queryFactory.select(qBankExcelSource.count()).where(booleanBuilder).fetchOne();
            return result("", list, count);
        }
        return result("type error");
    }

    public JSONObject pagePerson(PageParam param) {
        BooleanBuilder booleanBuilder = new BooleanBuilder();

        if (StringUtils.isNotBlank(param.contains)) {
            booleanBuilder.and(qExcelPerson.name.contains(param.contains));
        }
        Long count = queryFactory.select(qExcelPerson.count()).from(qExcelPerson).where(booleanBuilder).fetchFirst();
        List<ExcelPerson> persons = queryFactory.selectFrom(qExcelPerson).where(booleanBuilder).orderBy(qExcelPerson.id.desc()).offset(param.pageSize * (param.pageNumber - 1)).limit(param.pageSize).fetch();
        return result("", persons, count);
    }

    public JSONObject sendAndIncomeStatistics(PageParam param) {
        String caseNum = param.getCaseNum();
        Integer personId = param.getPersonId();
        List<String> typeNameList = queryFactory.select(qExcelSourceType.name).from(qExcelSourceType).rightJoin(qExcelSourceTypeField).on(qExcelSourceType.id.eq(qExcelSourceTypeField.typeId)).where(qExcelSourceTypeField.amountField.length().gt(0)).fetch();
        String typeName = param.getTypeName();

        if (caseNum == null || personId == null) {
            return result("缺少案件号或人员参数");
        }
        JSONObject res = new JSONObject();
        if (typeName == null) {
            typeNameList.forEach(item -> {
                param.setTypeName(item);
//                res.put(item, sendAndIncome(param));
                sendAndIncome(param, res);
            });
        } else if (!typeNameList.contains(typeName)) {
            return result("该类型不支持收入支出分析");
        } else {
            sendAndIncome(param, res);
        }
        return result("", res);
    }

    public void sendAndIncome(PageParam param, JSONObject res) {
        ExcelSourceType excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.name.eq(param.typeName)).fetchFirst();
        ExcelSourceType parent = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.id.eq(excelSourceType.getParentId())).fetchFirst();
        ExcelSourceTypeField excelSourceTypeField = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getId())).fetchFirst();
//        Integer personId = personIdMapping(param.personId);
        Integer personId = param.personId;
        List<String> numberList = queryFactory.select(qExcelPersonInfo.number).from(qExcelPersonInfo).where(qExcelPersonInfo.type.eq(excelSourceTypeField.getNumberType()).and(qExcelPersonInfo.personId.eq(personId))).fetch().stream().map(r -> r.replace("'", "\\'")).collect(Collectors.toList());
        String sql = "select sum(#{amountField}) as sum from #{tableName}  where case_num='#{caseNum}'  and #{sendNumberField} in (#{numberList}) and #{amountFlagField} like '%#{amountFlag}%'";
        sql = sql.replace("#{tableName}", excelSourceType.getTableName()).replace("#{amountField}", excelSourceTypeField.getAmountField()).replace("#{caseNum}", param.caseNum).replace("#{sendNumberField}", excelSourceTypeField.getSendNumberField()).replace("#{numberList}", "'" + String.join("','", numberList) + "'").replace("#{amountFlagField}", excelSourceTypeField.getAmountFlagField());
        String sendAmountSQL = sql.replace("#{amountFlag}", excelSourceTypeField.getSendAmountFlag());
        String receiveAmountSQL = sql.replace("#{amountFlag}", excelSourceTypeField.getReceiveAmountFlag());
        BigDecimal sendAmount = (BigDecimal) dynamicQueryUtil.query(sendAmountSQL).get(0).get("sum");
        BigDecimal receiveAmount = (BigDecimal) dynamicQueryUtil.query(receiveAmountSQL).get(0).get("sum");
        if (sendAmount == null) {
            sendAmount = new BigDecimal("0.00");
        }
        if (receiveAmount == null) {
            receiveAmount = new BigDecimal("0.00");
        }
        String typeName = param.getTypeName();
        if (parent != null) {
            typeName = parent.getName();
        }
        if (res.get(typeName) != null) {
            sendAmount = res.getJSONObject(typeName).getBigDecimal("支出").add(sendAmount);
            receiveAmount = res.getJSONObject(typeName).getBigDecimal("收入").add(receiveAmount);
        }

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("支出", String.format("%.2f", sendAmount));
        jsonObject.put("收入", String.format("%.2f", receiveAmount));
        res.put(typeName, jsonObject);
    }

    public JSONObject caseTimeline(PageParam param) {
        Map<String, Map<String, Object>> res = new TreeMap<>(String::compareTo);
        String caseNum = param.caseNum;
        List<Map<String, Object>> typeFields = dynamicQueryUtil.query("select est.name,est.table_name,estf.* from excel_source_type est right join excel_source_type_field estf on est.id = estf.type_id where date_field_format is not null");
        for (Map<String, Object> typeField : typeFields) {
            String typeName = String.valueOf(typeField.get("name"));
            String tableName = String.valueOf(typeField.get("table_name"));
            String dateField = String.valueOf(typeField.get("date_field"));
            String dateFieldFormat = String.valueOf(typeField.get("date_field_format"));
            String sendNameField = String.valueOf(typeField.get("send_name_field"));
            String receiveNameField = String.valueOf(typeField.get("receive_name_field"));
            List<Map<String, Object>> list = dynamicQueryUtil.query("select *,STR_TO_DATE(" + dateField + ",'" + dateFieldFormat + "') as date from " + tableName + " where case_num='" + caseNum + "'");
            list.forEach(item -> {
                String date = String.valueOf(item.get("date"));
                if (!res.containsKey(date)) {
                    res.put(date, new HashMap<>());
                }
                Map<String, Object> map = res.get(date);
                if (!map.containsKey(typeName)) {
                    map.put(typeName, new HashMap<>());
                }
                Map<String, Integer> dataMap = (Map<String, Integer>) map.get(typeName);
                String dataItem = item.get(sendNameField) + ":" + item.get(receiveNameField);
                if (dataMap.containsKey(dataItem)) {
                    dataMap.put(dataItem, dataMap.get(dataItem) + 1);
                } else {
                    dataMap.put(dataItem, 1);
                }
            });
        }
        return result("", res);

    }


    public JSONObject frequentPerson(PageParam param) {
        List<Map<String, Object>> typeList = dynamicQueryUtil.query("select est.name,est.table_name,estf.send_number_field,estf.receive_number_field,estf.number_type\n" +
                "from excel_source_type est\n" +
                "         left join excel_source_type_field estf on est.id = estf.type_id\n" +
                "where estf.id in (select max(id) from excel_source_type_field group by type_id)");

        Map<Integer, Map<String, Object>> maps = new HashMap<>();
        typeList.forEach(type -> {
            if (type.get("send_number_field") == null || type.get("receive_number_field") == null) {
                return;
            }
            String sql =
                    "select receive_person_id,receive_name,count(1) as count from (\n" +
                            "select spi.person_id as send_person_id\n" +
                            "     ,receive.person_id as receive_person_id," +
                            "t.receive_name as receive_name\n" +
                            "from #{tableName} t\n" +
                            "         left join excel_person_info spi on t.#{sendNumber} = spi.number and spi.type = '#{numberType}'\n" +
                            "         left join (select t.id,number,person_id\n" +
                            "                    from #{tableName} t\n" +
                            "                             left join excel_person_info spi on t.#{receiveNumber} = spi.number and spi.type = '#{numberType}' where  number is not null ) receive on t.id = receive.id) as send_and_receive_person_id\n" +
                            "where send_person_id=#{personId} and receive_person_id is not null\n" +
                            "group by send_person_id,receive_person_id,receive_name having count(1) > 0";
            sql = sql.replace("#{personId}", String.valueOf(param.personId)).replace("#{tableName}", String.valueOf(type.get("table_name"))).replace("#{sendNumber}", String.valueOf(type.get("send_number_field"))).replace("#{receiveNumber}", String.valueOf(type.get("receive_number_field"))).replace("#{numberType}", String.valueOf(type.get("number_type")));
            List<Map<String, Object>> receivePersonList = dynamicQueryUtil.query(sql);
            receivePersonList.forEach(receivePerson -> {
                Integer receivePersonId = (Integer) receivePerson.get("receive_person_id");
                if (!maps.containsKey(receivePersonId)) {
                    maps.put(receivePersonId, new HashMap<>());
                    maps.get(receivePersonId).put("records", new HashMap<>());
                    maps.get(receivePersonId).put("name", receivePerson.get("receive_name"));
                    maps.get(receivePersonId).put("person_id", receivePersonId);
                }
                String typeName = String.valueOf(type.get("name"));
                Long count = (Long) receivePerson.get("count");
                Map records = (Map) maps.get(receivePersonId).get("records");
                if (records.containsKey(typeName)) {
                    records.put(typeName, (long) records.get(typeName) + count);
                } else {
                    records.put(typeName, count);
                }

            });
        });

        return result("", maps.values());

    }

    public JSONObject page(PageParam param, HttpServletRequest request) {
//        long userId = 0;
//        String username = "";
//        try {
//            ValidToken validToken = userService.getValidToken(request);
//            if (validToken.getValid()) {
//                userId = validToken.getUserId();
//                username = validToken.getUsername();
//            }
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }

//        List<String> list = new ArrayList<>();
//        list.add("tj");
//        list.add("fjw");
//        list.add("cjz");
//        list.add("dhj");
//        list.add("wangjian");
        BooleanBuilder booleanBuilder = new BooleanBuilder();
//        if (userId != 0) {
//            condition = field("user_id").eq(userId);
//        }
//        if (list.contains(username)) {
//            List<Long> orgIds = dslContext.select(RL_USER_ORG.ORG_ID).from(RL_USER_ORG).where(RL_USER_ORG.USER_ID.eq((int) userId)).fetchInto(Long.class);
//            List<Long> userIds = dslContext.select(RL_USER_ORG.USER_ID).from(RL_USER_ORG).where(RL_USER_ORG.ORG_ID.in(orgIds)).fetchInto(Long.class);
//            condition = EXCEL_SOURCE_RECORD.USER_ID.in(userIds);
//        }
        if (StringUtils.isNotBlank(param.typeName)) {
            if (param.typeName.contains(",")) {
                BooleanBuilder sBooleanBuilder = new BooleanBuilder();
                String[] typeNames = StringUtils.split(param.typeName, ",");
                for (String typeName : typeNames) {
                    sBooleanBuilder.or(qExcelSourceRecord.typeName.contains(typeName));
                }
                booleanBuilder.and(sBooleanBuilder);
            } else {
                booleanBuilder.and(qExcelSourceRecord.typeName.contains(param.typeName));
            }
        }
        Long total = queryFactory.select(qExcelSourceRecord.count()).from(qExcelSourceRecord).where(booleanBuilder).fetchOne();
        List<ExcelSourceRecord> l = queryFactory.selectFrom(qExcelSourceRecord).where(booleanBuilder).orderBy(qExcelSourceRecord.id.desc()).offset(param.pageSize * (param.pageNumber - 1)).limit(param.pageSize).fetch();
        JSONObject res = result("", l);
        res.put("total", total);
        return res;
    }

    //    public Integer personIdMapping(Integer personId) {
//        List<String> phoneNumberList = queryFactory.select()
//                dslContext.select(TB_PHONE_NUMBER.PHONE_NUMBER).from(TB_PHONE_NUMBER).where(TB_PHONE_NUMBER.PERSON_ID.eq(personId)).fetchInto(String.class);
//        return dslContext.select(STATISTICS_PERSON_INFO.PERSON_ID).from(STATISTICS_PERSON_INFO).where(STATISTICS_PERSON_INFO.NUMBER.in(phoneNumberList)).fetchAnyInto(Long.class);
//    }
    public JSONObject pageData(PageParam param) {
        Map<String, Object> condition = new HashMap<>();
        condition.put("record_id_equal", param.recordId);
        Integer typeId = queryFactory.select(qExcelSourceRecord.typeId).from(qExcelSourceRecord).where(qExcelSourceRecord.id.eq(param.recordId)).fetchOne();
        String tableName = queryFactory.select(qExcelSourceType.tableName).from(qExcelSourceType).where(qExcelSourceType.id.eq(typeId)).fetchOne();
        List<ExcelSourceTypeMapping> excelSourceTypeMappings = queryFactory.selectFrom(qExcelSourceTypeMapping).where(qExcelSourceTypeMapping.typeId.eq(typeId)).fetch();
        Map<String, String> columns = new TreeMap<>();
        excelSourceTypeMappings.forEach(mapping -> {
            String toKey = mapping.getToKey();
            String fromKey = mapping.getFromKey();
            columns.put(toKey, fromKey);
        });
        Long total = dynamicQueryUtil.queryCount(tableName, condition);
        List<Map<String, Object>> l = dynamicQueryUtil.query(tableName, columns, condition, null, param.pageSize * (param.pageNumber - 1), param.pageSize);
        JSONObject res = result("", l);
        res.put("total", total);
        return res;
    }

    public JSONObject delete(Integer id) {
        ExcelSourceRecord excelSourceRecord = queryFactory.selectFrom(qExcelSourceRecord).where(qExcelSourceRecord.id.eq(id)).fetchOne();
        String tableName = queryFactory.select(qExcelSourceType.tableName).from(qExcelSourceType).where(qExcelSourceType.id.eq(excelSourceRecord.getTypeId())).fetchOne();
        String fileName = excelSourceRecord.getSaveName();
        File file = new File(appConfig.getAnalyzeAndStatisticsDir() + fileName);
        file.deleteOnExit();
        File resultFile = new File(appConfig.getAnalyzeAndStatisticsDir() + "result-" + fileName);
        resultFile.deleteOnExit();
        dynamicQueryUtil.execute("delete from " + tableName + " where record_id=" + id);

        excelSourceRecordService.deleteById(excelSourceRecord.getId());
        return result("删除成功");
    }


    public void processJobBookByName(List<Map> data, List<ExcelSourceTypeMapping> mappingList, ExcelSourceRecord excelSourceRecordRecord, Integer personId, String caseNum) {
        try {
            StringBuilder insertSQL = new StringBuilder("insert into excel_person(name,id_card,job_name) values");
            StringBuilder updateSQL = new StringBuilder();

            data.forEach(item -> {
                Map<String, Object> m = MapTransferUtil.transfer(item, mappingList);
                String name = String.valueOf(m.get("name"));
                String jobName = String.valueOf(m.get("job_name"));
                insertSQL.append(" ('").append(name).append("','','").append(jobName).append("'),");
                updateSQL.append("update excel_person set job_name='").append(jobName).append("' where name='").append(name).append("';");
            });
            insertSQL.deleteCharAt(insertSQL.length() - 1);
            insertSQL.append(" ON DUPLICATE KEY UPDATE id=id;");
            dynamicQueryUtil.execute(insertSQL.toString());
            dynamicQueryUtil.execute(updateSQL.toString());

            // 通过工具类创建writer
//            AtomicReference<ExcelWriter> writer = new AtomicReference<>(null);

            excelSourceRecordRecord.setStatus("已完成");
            // 关闭writer，释放内存
//            writer.get().close();
        } catch (Exception e) {
            excelSourceRecordRecord.setStatus(e.getMessage());
            e.printStackTrace();
        } finally {
            excelSourceRecordService.save(excelSourceRecordRecord);
        }
    }


    public void processJobBookByPhone(List<Map> data, List<ExcelSourceTypeMapping> mappingList, ExcelSourceRecord excelSourceRecord, String caseNum) {
        try {
            StringBuilder insertPersonSQL = new StringBuilder("insert into excel_person(name,id_card,job_name,address_book_name) values");
            StringBuilder insertPersonInfoSQL = new StringBuilder("insert into excel_person_info(person_id,type,number) values");

            data.forEach(item -> {
                StringBuilder tempUpdateSQL = new StringBuilder();

                StringBuilder tempInsertPersonSQL = new StringBuilder();
                StringBuilder tempInsertPersonInfoSQL = new StringBuilder();
                Map<String, Object> m = MapTransferUtil.transfer(item, mappingList);
                String name = String.valueOf(m.get("address_book_name"));
                String jobName = String.valueOf(m.get("job_name"));
                String number = String.valueOf(m.get("number"));
                Integer personId = queryFactory.select(qExcelPersonInfo.personId).from(qExcelPersonInfo).where(qExcelPersonInfo.number.eq(number).and(qExcelPersonInfo.type.eq("手机号"))).fetchFirst();
                if (personId != null) {
                    tempUpdateSQL.append("update excel_person set job_name='").append(jobName).append("',address_book_name='").append(name).append("' where id='").append(personId).append("';");
                    dynamicQueryUtil.execute(tempUpdateSQL.toString());
                } else {
                    tempUpdateSQL.append("update excel_person set job_name='").append(jobName).append("',address_book_name='").append(name).append("' where name='").append(name).append("';");
                    dynamicQueryUtil.execute(tempUpdateSQL.toString());

                    tempInsertPersonSQL.append(insertPersonSQL).append(" ('").append(name).append("','','").append(jobName).append("','").append(name).append("') ").append(" ON DUPLICATE KEY UPDATE id=id;");
                    dynamicQueryUtil.execute(tempInsertPersonSQL.toString());

                    personId = queryFactory.select(qExcelPerson.id).from(qExcelPerson).where(qExcelPerson.addressBookName.eq(name)).fetchFirst();
                    tempInsertPersonInfoSQL.append(insertPersonInfoSQL).append(" ('").append(personId).append("','手机号','").append(number).append("')");
                    dynamicQueryUtil.execute(tempInsertPersonInfoSQL.toString());
                }
            });

            // 通过工具类创建writer
//            AtomicReference<ExcelWriter> writer = new AtomicReference<>(null);

            excelSourceRecord.setStatus("已完成");
            // 关闭writer，释放内存
//            writer.get().close();
        } catch (Exception e) {
            excelSourceRecord.setStatus(e.getMessage());
            e.printStackTrace();
        } finally {
            excelSourceRecordService.save(excelSourceRecord);
        }
    }


    // 在excel的数据被插入到数据库前进行修改
    public void processDataItem(Map<String, Object> m) {
//        if (m.containsKey("send_amount")) {
//            BigDecimal sendAmount = new BigDecimal(String.valueOf(m.get("send_amount")));
//            if (sendAmount.longValue() > 0) {
//                m.put("transaction_amount", sendAmount);
//                m.put("load_type", "借");
//            }
//        } else if (m.containsKey("receive_amount")) {
//            BigDecimal receiveAmount = new BigDecimal(String.valueOf(m.get("receive_amount")));
//            if (receiveAmount.longValue() > 0) {
//                m.put("transaction_amount", receiveAmount);
//                m.put("load_type", "贷");
//            }
//        }
    }


    public void processData(List<Map> data, List<ExcelSourceTypeMapping> mappingList, ExcelSourceRecord excelSourceRecord, Integer personId, String caseNum, String sendName, String sendNumber, ExcelReader reader, String auth) {
        try {
            Integer finalRecordId = excelSourceRecord.getId();
            StringBuilder insertSQL = new StringBuilder("insert into ");
            Set keySet = mappingList.stream().map(ExcelSourceTypeMapping::getToKey).collect(Collectors.toSet());
            ExcelSourceType excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.id.eq(excelSourceRecord.getTypeId())).fetchOne();
            ExcelSourceTypeField tempExcelSourceTypeField = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getId())).fetchFirst();
            if (tempExcelSourceTypeField == null) {
                tempExcelSourceTypeField = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getParentId())).fetchFirst();
            }
            final ExcelSourceTypeField excelSourceTypeField = tempExcelSourceTypeField;
            String sendNumberRowColumn = excelSourceType.getSendNumberRowColumn();
            String sendNameRowColumn = excelSourceType.getSendNameRowColumn();


            if (StringUtils.isNotBlank(sendNameRowColumn)) {
                List<Integer> list = Arrays.stream(sendNameRowColumn.split(",")).map(Integer::valueOf).collect(Collectors.toList());
                sendName = String.valueOf(reader.readCellValue(list.get(0), list.get(1)));
            }
//            else {
//                String toKey = excelSourceTypeField.getSendNameField();
//                Optional<String> fromKeyOptional = mappingList.stream().filter(r->Objects.equals(r.getToKey(),toKey)).map(ExcelSourceTypeMapping::getFromKey).findFirst();
//                if (fromKeyOptional.isPresent()) {
//                    String fromKey = fromKeyOptional.get();
//                    Optional<Object> sendNameOptional = data.stream().map(r ->
//                            r.get(fromKey)
//                    ).filter(Objects::nonNull).findFirst();
//                    if (sendNameOptional.isPresent()) {
//                        sendName = String.valueOf(sendNameOptional.get());
//                    }
//                }
//            }
            if (StringUtils.isNotBlank(sendNumberRowColumn)) {
                List<Integer> list = Arrays.stream(sendNumberRowColumn.split(",")).map(Integer::valueOf).collect(Collectors.toList());
                sendNumber = String.valueOf(reader.readCellValue(list.get(0), list.get(1)));
            }
//            else {
//                String toKey = excelSourceTypeField.getSendNumberField();
//                Optional<String> fromKeyOptional = mappingList.stream().filter(r->Objects.equals(r.getToKey(),toKey)).map(ExcelSourceTypeMapping::getFromKey).findFirst();
//                if (fromKeyOptional.isPresent()) {
//                    String fromKey = fromKeyOptional.get();
//                    Optional<Object> sendNumberOptional = data.stream().map(r ->
//                            r.get(fromKey)
//                    ).filter(Objects::nonNull).findFirst();
//                    if (sendNumberOptional.isPresent()) {
//                        sendNumber = String.valueOf(sendNumberOptional.get());
//                    }
//                }
//            }
            String tableName = excelSourceType.getTableName();
            insertSQL.append(tableName).append("(").append(StringUtils.join(keySet, ","));
            ExcelPerson excelPerson = null;
            if (sendName != null) {
                insertSQL.append(",").append(excelSourceTypeField.getSendNameField());
//                excelPerson = queryFactory.selectFrom(qExcelPerson).where(qExcelPerson.name.eq(sendName).and(qExcelPerson.caseNum.eq(caseNum))).fetchFirst();
//                if (excelPerson == null) {
//                    String code = "time" + System.currentTimeMillis();
//                    excelPerson = new ExcelPerson();
//                    excelPerson.setName(sendName);
//                    excelPerson.setCaseNum(caseNum);
//                    excelPerson.setCode(code);
//                    excelPersonService.save(excelPerson);
//                    ExcelPersonCase excelPersonCase = new ExcelPersonCase();
//                    excelPersonCase.setCaseNum(caseNum);
//                    excelPersonCase.setPersonCode(code);
//                    excelPersonCaseService.save(excelPersonCase);
//                }
                if (personId == null || personId == 0) {
                    personId = excelPerson.getId();
                }
            }
            ExcelPersonInfo excelPersonInfo = new ExcelPersonInfo();
            if (sendNumber != null) {
                insertSQL.append(",").append(excelSourceTypeField.getSendNumberField());
//                excelPersonInfo = queryFactory.selectFrom(qExcelPersonInfo).where(qExcelPersonInfo.caseNum.eq(caseNum).and(qExcelPersonInfo.number.eq(sendNumber).and(qExcelPersonInfo.personId.eq(excelPerson.getId())))).fetchFirst();
//                if (excelPersonInfo == null) {
//                    excelPersonInfo = new ExcelPersonInfo();
//                    excelPersonInfo.setNumber(sendNumber);
//                    excelPersonInfo.setPersonId(excelPerson.getId());
//                    excelPersonInfo.setCaseNum(caseNum);
//                    excelPersonInfoService.save(excelPersonInfo);
//                }
            }
            if (personId != null) {
                insertSQL.append(",person_id");
            }
            insertSQL.append(",case_num,record_id) values");
            String finalSendNumber = sendNumber;
            String finalSendName = sendName;
            Integer finalPersonId = personId;
            data.forEach(item -> {
                Map<String, Object> m = MapTransferUtil.transfer(item, mappingList);
                processDataItem(m);
                m.put("record_id", finalRecordId);

                insertSQL.append("(");
                keySet.forEach(key -> {
                    String value = String.valueOf(m.get(key)).replace("'", "\\'");
                    if ("null".equals(value)) {
                        value = "";
                    }
                    if (excelSourceTypeField != null && key.equals(excelSourceTypeField.getReceiveNameField()) && value.isEmpty()) {
                        if (m.get(excelSourceTypeField.getSendNameField()) != null) {
                            value = String.valueOf(m.get(excelSourceTypeField.getSendNameField()));
                        }
                        if (finalSendName != null) {
                            value = finalSendName;
                        }
                        value += "-未知联系人";
                        value = String.valueOf(m.get(excelSourceTypeField.getReceiveNumberField()));
                    }
                    value = value.replace("'", "");
                    insertSQL.append("'").append(value).append("',");
                });

                if (finalSendName != null) {
                    insertSQL.append("'").append(finalSendName).append("',");
                }
                if (finalSendNumber != null) {
                    insertSQL.append("'").append(finalSendNumber).append("',");
                }
                if (finalPersonId != null) {
                    insertSQL.append("'").append(finalPersonId).append("',");
                }
                insertSQL.append("'").append(caseNum).append("',").append(finalRecordId).append("),");
            });
            insertSQL.deleteCharAt(insertSQL.length() - 1);
            // 文件数据入库
            dynamicQueryUtil.execute(insertSQL.toString());

            // 人员和人员信息入库
            processPerson(finalRecordId, personId, excelSourceType, caseNum, auth);

            // 通过工具类创建writer
//            AtomicReference<ExcelWriter> writer = new AtomicReference<>(null);

            excelSourceRecord.setStatus("已完成");
            // 关闭writer，释放内存
//            writer.get().close();
        } catch (Exception e) {
            excelSourceRecord.setStatus(e.getMessage());
            e.printStackTrace();
        } finally {
            excelSourceRecordService.save(excelSourceRecord);
        }
    }

    public void processPerson(Integer recordId, Integer personId, ExcelSourceType excelSourceType, String caseNum, String auth) {
        List<ExcelSourceTypeField> excelSourceTypeFields = queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getId())).fetch();
        if (excelSourceTypeFields.isEmpty()) {
            excelSourceTypeFields.add(queryFactory.selectFrom(qExcelSourceTypeField).where(qExcelSourceTypeField.typeId.eq(excelSourceType.getParentId())).fetchFirst());
        }
        excelSourceTypeFields.forEach(excelSourceTypeField -> processPersonByType(recordId, personId, excelSourceType, excelSourceTypeField, caseNum, auth));

    }

    public void processPersonByType(Integer recordId, Integer personId, ExcelSourceType
            excelSourceType, ExcelSourceTypeField excelSourceTypeField, String caseNum, String auth) {
        String sql1 = selectPersonSQL(excelSourceType.getTableName(), excelSourceTypeField.getSendNameField(), excelSourceTypeField.getSendNumberField());
        String sql2 = selectPersonSQL(excelSourceType.getTableName(), excelSourceTypeField.getReceiveNameField(), excelSourceTypeField.getReceiveNumberField());

        try {
            if (!sql2.isEmpty()) {
                sql2 = sql2.replace("#recordId", String.valueOf(recordId));
                List<Map<String, Object>> personList = dynamicQueryUtil.query(sql2);
                processPersonInfo(personList, recordId, personId, excelSourceTypeField.getNumberType(), 0, caseNum, auth);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            if (!sql1.isEmpty()) {
                sql1 = sql1.replace("#recordId", String.valueOf(recordId));
                List<Map<String, Object>> personList = dynamicQueryUtil.query(sql1);
                processPersonInfo(personList, recordId, personId, excelSourceTypeField.getNumberType(), 1, caseNum, auth);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }


//        pushGraph(recordId, personId, excelSourceType, excelSourceTypeField);
    }

    public String selectPersonSQL(String tableName, String nameField, String numberField) {
        if ((nameField == null && numberField == null) || tableName == null) {
            return "";
        }

        String selectNameField = "''";
        String selectNumberField = "''";
        if (nameField != null) {
            selectNameField = nameField;
        }
        if (numberField != null) {
            selectNumberField = numberField;
        }
        String sql = "select " + selectNameField + " as name," + selectNumberField + " as number from " + tableName + " where record_id=#recordId and length(" + nameField + ") > 0 and length(" + numberField + ") > 0 group by " + nameField + "," + numberField + "\n";
        if (nameField == null) {
            sql = sql.replace("null", numberField);
        } else if (numberField == null) {
            sql = sql.replace("null", nameField);
        }
        return sql;
    }

    @Transactional
    public void processPersonInfo(List<Map<String, Object>> personList, Integer finalRecordId, Integer personId, String numberType, Integer isRespondents, String caseNum, String auth) {
        for (Map<String, Object> person : personList) {
            String name = String.valueOf(person.get("name"));
            String number = String.valueOf(person.get("number"));
            String idCard = String.valueOf(person.getOrDefault("idCard", ""));
            if (StringUtils.isBlank(number)) {
                continue;
            }
            if (StringUtils.isBlank(name)) {
                name = number;
            }
            name = name.replace("'", "\\'");
            ExcelPerson excelPerson = new ExcelPerson();
            excelPerson.setName(name);
            excelPerson.setIdCard(idCard);
            excelPerson = excelPersonService.saveIfNotExists(excelPerson);
            excelPerson.setIsRespondents(isRespondents == 1);
            excelPersonService.save(excelPerson, auth);


            ExcelPersonCase excelPersonCase = new ExcelPersonCase();
            excelPersonCase.setCaseNum(caseNum);
            excelPersonCase.setPersonId(excelPerson.getId());
            excelPersonCaseService.saveIfNotExists(excelPersonCase, auth);

            ExcelPersonRecord excelPersonRecord = new ExcelPersonRecord();
            excelPersonRecord.setPersonId(excelPerson.getId());
            excelPersonRecord.setRecordId(finalRecordId);
            excelPersonRecordService.saveIfNotExists(excelPersonRecord, auth);

            ExcelPersonInfo excelPersonInfo = new ExcelPersonInfo();
            excelPersonInfo.setNumber(number);
            excelPersonInfo.setType(numberType);
            excelPersonInfo.setPersonId(excelPerson.getId());
            excelPersonInfo = excelPersonInfoService.saveIfNotExists(excelPersonInfo, auth);
//            dynamicQueryUtil.execute("INSERT INTO excel_person (name,id_card,is_respondents,create_time)\n" +
//                    "values ('" + name + "','" + person.getOrDefault("idCard", "") + "'," + isRespondents + ",'" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) + "')\n" +
//                    "ON DUPLICATE KEY UPDATE id=id;" +
//                    "update excel_person set is_respondents=" + isRespondents + " where name='" + name + "' and id_card='" + person.getOrDefault("idCard", "") + "';"
//            );
//            ExcelPerson excelPerson = queryFactory.selectFrom(qExcelPerson).where(qExcelPerson.name.eq(name)).fetchFirst();
//            if (excelPerson != null && excelPerson.getName() != null) {
//                personId = excelPerson.getId();
//
//                dynamicQueryUtil.execute(
//                        "INSERT INTO excel_person_case (name,case_num,person_id)\n" +
//                                "values ('" + name + "','" + caseNum + "' ,"+personId+ ")\n" +
//                                "ON DUPLICATE KEY UPDATE id=id;");
//            }
//
//            number = number.replace("'", "\\'");
//            if (StringUtils.isNotBlank(number)) {
//                dynamicQueryUtil.execute("INSERT INTO excel_person_info (type,number,person_id,record_id)\n" +
//                        "values ('" + numberType + "','" + number + "', '" + personId + "','" + finalRecordId + "')\n" +
//                        "ON DUPLICATE KEY UPDATE id=id;");
//            }
        }

    }

    public void pushGraph(Integer recordId, Integer personId, ExcelSourceType excelSourceType, ExcelSourceTypeField
            excelSourceTypeField) {
        String typeName = getTopTypeName(excelSourceType);
        String tableName = excelSourceType.getTableName();
        Map<String, Integer> numberAndPersonIdMap = new HashMap<>();
        Map<Integer, List<ExcelPersonInfo>> personIdAndInfoListMap = new HashMap<>();

        List<ExcelPersonInfo> personInfoList = queryFactory.selectFrom(qExcelPersonInfo).where(qExcelPersonInfo.type.eq(excelSourceTypeField.getNumberType())).fetch();

        personInfoList.forEach(personInfo -> {
            numberAndPersonIdMap.putIfAbsent(personInfo.getNumber(), personInfo.getPersonId());
            if (personIdAndInfoListMap.containsKey(personInfo.getPersonId())) {
                personIdAndInfoListMap.get(personInfo.getPersonId()).add(personInfo);
            } else {
                List<ExcelPersonInfo> list = new ArrayList<>();

                list.add(personInfo);
                personIdAndInfoListMap.put(personInfo.getPersonId(), list);
            }
        });

        String selectSourceSQL = "select * from " + tableName + " where record_id=" + recordId;
        List<Map<String, Object>> sourceList = dynamicQueryUtil.query(selectSourceSQL);
        String name = "";
        if (personId != null) {
            name = queryFactory.select(qExcelPerson.name).from(qExcelPerson).where(qExcelPerson.id.eq(personId)).fetchOne();
        }
        String finalName = name;

        List<JSONObject> paramList = new ArrayList<>();
        sourceList.forEach(source -> {
            String sendName = String.valueOf(source.get(excelSourceTypeField.getSendNameField()));
            String receiveName = String.valueOf(source.get(excelSourceTypeField.getReceiveNameField()));
            String sendNumber = String.valueOf(source.get(excelSourceTypeField.getSendNumberField()));
            String receiveNumber = String.valueOf(source.get(excelSourceTypeField.getReceiveNumberField()));
            Integer sendPersonId = numberAndPersonIdMap.get(sendNumber);
            if (sendPersonId == null) {
                sendPersonId = personId;
            }
            if (sendName == null) {
                sendName = finalName;
            }
            Integer receivePersonId = numberAndPersonIdMap.get(receiveNumber);
            List<ExcelPersonInfo> sendPersonInfoList = personIdAndInfoListMap.get(sendPersonId);
            List<ExcelPersonInfo> receivePersonInfoList = personIdAndInfoListMap.get(receivePersonId);

            JSONObject param = new JSONObject();
            JSONObject from = new JSONObject();
            from.put("id", sendPersonId);
            from.put("name", sendName);
            from.put("personInfoList", JSONArray.toJSONString(sendPersonInfoList));
            JSONObject to = new JSONObject();
            to.put("id", receivePersonId);
            to.put("name", receiveName);
            to.put("personInfoList", JSONArray.toJSONString(receivePersonInfoList));

            param.put("from", from);
            param.put("to", to);
            if (excelSourceType.getTypeNameField() != null) {
                source.put("relType", source.get(excelSourceType.getTypeNameField()));
                if (excelSourceType.getTypeNameTransfer() != null) {
                    String[] tArray = excelSourceType.getTypeNameTransfer().split(":");
                    String[] keyArray = tArray[0].split(",");
                    String[] valueArray = tArray[1].split(",");
                    for (int i = 0; i < keyArray.length; i++) {
                        if (keyArray[i].equals(source.get(excelSourceType.getTypeNameField()))) {
                            source.put("relType", valueArray[i]);
                            break;
                        }
                    }
                }
            }
            source.put("excelSourceTypeName", excelSourceType.getName());
            param.put("rel", source);
            param.put("relType", typeName);

            if (sendPersonId != null && receivePersonId != null) {
                paramList.add(param);
            }
        });
        paramList.forEach(param -> {
            RequestBody requestBody = RequestBody.create(MediaType.parse("application/json;charset=utf-8"), param.toJSONString());
            Request request = new Request.Builder()
                    .url(appConfig.getGraphUrl() + "/insert")
                    .post(requestBody)
                    .build();

            try {
                Call call = okHttpClient.newCall(request);
                String response = call.execute().body().string();
                System.out.println(response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
    }

    public String getTopTypeName(ExcelSourceType excelSourceType) {
        Integer parentId = excelSourceType.getParentId();
        while (parentId != null) {
            excelSourceType = queryFactory.selectFrom(qExcelSourceType).where(qExcelSourceType.id.eq(parentId)).fetchFirst();
            parentId = excelSourceType.getParentId();
        }
        return excelSourceType.getName();
    }

    public File getFile(Integer id, String type) {
        String fileName = queryFactory.select(qExcelSourceRecord.saveName).from(qExcelSourceRecord).where(qExcelSourceRecord.id.eq(id)).fetchFirst();
        if (type.equals("result")) {
            fileName = "result-" + fileName;
        }
        fileName = appConfig.getAnalyzeAndStatisticsDir() + fileName;
        return new File(fileName);
    }

    public List<Map> processPDF(String filePath) throws IOException {
        PdfReader reader = null;
        reader = new PdfReader(filePath);

        PdfReaderContentParser pdfReaderContentParser = new PdfReaderContentParser(reader);
        List<List<BillService.TextLineMode>> textLineModeList = new ArrayList<>();
        for (int i = 1; i <= reader.getNumberOfPages(); i++) {
            List<BillService.TextLineMode> list = new ArrayList<>();
            pdfReaderContentParser.processContent(i, new RenderListener() {
                @Override
                public void beginTextBlock() {
                }

                @Override
                public void renderText(TextRenderInfo textRenderInfo) {
                    String text = textRenderInfo.getText();
                    com.itextpdf.awt.geom.Rectangle2D.Float bound = textRenderInfo.getBaseline().getBoundingRectange();
                    final double x = bound.getX();
                    final double y = bound.getY();
                    list.add(new BillService.TextLineMode(text.trim(), x, y));
//                    System.out.println("x=" + x + ", y=" + y + ", text=" + text);
                }

                @Override
                public void endTextBlock() {
                }

                @Override
                public void renderImage(ImageRenderInfo imageRenderInfo) {
                }
            });
            textLineModeList.add(list);
        }
        // 先按照X轴排序,取得第一条
        StringBuilder documents = new StringBuilder();
        textLineModeList.forEach(list -> {
            List<String> newList = list.stream().map(BillService.TextLineMode::toString).collect(Collectors.toList());
            String document = String.join("\n", newList);
            documents.append(document);
        });
        List<Map> res = new ArrayList<>(RtsUtilv2.remoldRts(documents.toString()));
//        for (List<BillService.TextLineMode> textLineModes : textLineModeList) {
//            for (BillService.TextLineMode textLineMode : textLineModes) {
//                System.out.println(textLineMode.getText());
//            }
//        }

        String sendName = "";
        String sendPhone = "";
        for (BillService.TextLineMode item : textLineModeList.get(0)) {
            String str = item.getText();
            if (str.contains("客户姓名")) {
                sendName = str.replace("客户姓名", "").replace(":", "").replace("：", "");
            }
            if (str.contains("号码")) {
                sendPhone = str.replace("号码", "").replace(":", "").replace("：", "");
            }
            if (!sendName.isEmpty() && !sendPhone.isEmpty()) {
                break;
            }
        }
        Map<String, String> map = new HashMap<>();
        map.put("send_name", sendName);
        map.put("send_phone", sendPhone);
        res.add(0, map);
        return res;
    }

    // 提供给数据源服务的api
//    public void processDataApi(List<Map> data, List<Map<String, Object>> mappingList) {
//        ExcelSourceRecordRecord excelSourceRecordRecord = dslContext.newRecord(EXCEL_SOURCE_RECORD);
//        Integer recordId = 0;
//        try {
//            String suffix = "xlsx";
//            String name = System.currentTimeMillis() + "-" + "remote";
//            String fileName = name + "." + suffix;
//            String savePath = appConfig.getAnalyzeAndStatisticsDir() + fileName;
//
//            ExcelWriter writer = new ExcelWriter(savePath);
//            writer.write(data, true);
//            writer.close();
//
//            excelSourceRecordRecord.setName(name);
//            excelSourceRecordRecord.setSaveName(fileName);
//            excelSourceRecordRecord.setStatus("已完成");
//            excelSourceRecordRecord.setTypeName("remote");
//            excelSourceRecordRecord.insert();
//
//            recordId = dslContext.select(EXCEL_SOURCE_RECORD.ID).from(EXCEL_SOURCE_RECORD).orderBy(EXCEL_SOURCE_RECORD.ID.desc()).limit(1).fetchAnyInto(Long.class);
//            excelSourceRecordRecord.setId(recordId);
//
//            processData(data, mappingList, excelSourceRecordRecord,personId);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    private JSONObject result(String msg, Object data, Long count) {
        JSONObject res = new JSONObject();
        res.put("status", true);
        res.put("msg", msg);
        res.put("data", data);
        res.put("count", count);
        return res;
    }

//    private JSONObject result(String msg, Object data, Long count) {
//        JSONObject res = new JSONObject();
//        res.put("status", true);
//        res.put("msg", msg);
//        res.put("data", data);
//        res.put("count", count);
//        return res;
//    }


    private JSONObject result(String msg, Object data) {
        JSONObject res = new JSONObject();
        res.put("status", true);
        res.put("msg", msg);
        res.put("data", data);
        return res;
    }

    private JSONObject result(String msg) {
        JSONObject res = new JSONObject();
        res.put("status", true);
        res.put("msg", msg);
        return res;
    }

    @Data
    public static class PageParam {
        private String field;
        private String value;
        private Long pageSize;
        private Long pageNumber;
        private Long page;
        private String contains;
        private String type;
        private Integer recordId;
        private Integer personId;
        private Integer typeId;
        private String caseNum;
        private String typeName;
        private Integer sendPersonId;
        private Integer receivePersonId;
        private String sendName;
        private String sendNumber;
        private String receiveName;
        private String receiveNumber;
        private Map<String, Object> entity;
        private Map<String, Object> order;

    }

}
